package com.atrium.master.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.atrium.master.dao.GestorLibrosDao;
import com.atrium.master.modelo.Libro;

public class GestorLibrosDaoImpl extends JdbcDaoSupport implements GestorLibrosDao{

	public Libro agregarLibro(final Libro libro) {
		final String INSERT_SQL = "INSERT INTO libros (isbn,titulo,autor,descripcion) values(?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		getJdbcTemplate().update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(INSERT_SQL,
						new String[] { "id" });
				ps.setString(1, libro.getIsbn());
				ps.setString(2, libro.getTitulo());
				ps.setString(3, libro.getAutor());
				ps.setString(4, libro.getDescripcion());
				return ps;
			}
		}, keyHolder);
		libro.setId(keyHolder.getKey().intValue());
		return libro;
	}
	
	public Libro modificarDatosLibro(final Libro libro) {
		final String UPDATE_SQL = "update libros set titulo=?,autor=?,descripcion=? where isbn=?";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		getJdbcTemplate().update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(UPDATE_SQL,
						new String[] { "id" });
				ps.setString(1, libro.getIsbn());
				ps.setString(2, libro.getTitulo());
				ps.setString(3, libro.getAutor());
				ps.setString(4, libro.getDescripcion());
				return ps;
			}
		}, keyHolder);
		return libro;
	}

	public List<Libro> obtenerListadoLibros() {
		final String SELECT_SQL = "select * from libros";
		final List<Libro> listado = new LinkedList<Libro>();
		getJdbcTemplate().query(SELECT_SQL, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				Libro libro = new Libro(
						rs.getInt("id"), rs.getString("isbn"), 
						rs.getString("titulo"), rs.getString("autor"), 
						rs.getString("descripcion"));
				listado.add(libro);
			}
		});
		return listado;
	}

	public Libro obtenerLibroPorId(Integer id) {
		final String SELECT_SQL = "select * from libros where id=?";
		return getJdbcTemplate().queryForObject(SELECT_SQL,
				new RowMapper<Libro>() {
					public Libro mapRow(ResultSet rs, int rowNum)	throws SQLException {
						return new Libro(
							rs.getInt("id"), rs.getString("isbn"), 
							rs.getString("titulo"), rs.getString("autor"), 
							rs.getString("descripcion"));
					}
				}, new Object[] { id });
	}

	public Libro obtenerLibroPorISBN(String isbn) {
		final String SELECT_SQL = "select * from libros where isbn=?";
		return getJdbcTemplate().queryForObject(SELECT_SQL,
				new RowMapper<Libro>() {
					public Libro mapRow(ResultSet rs, int rowNum)	throws SQLException {
						return new Libro(
							rs.getInt("id"), rs.getString("isbn"), 
							rs.getString("titulo"), rs.getString("autor"), 
							rs.getString("descripcion"));
					}
				}, new Object[] { isbn });
	}
	
	public Integer borrarLibro(Integer id) {
		final String DELETE_SQL = "delete from libros where id=?";
		return getJdbcTemplate().update(DELETE_SQL, new Object[] { id });
	}
}